package cn.edu.sysu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sysu.dto.UserDto;
import cn.edu.sysu.util.Transaction;

public enum UserDao {

	INSTANCE;

	private static String GET_ALL_USERS = "select * from cws_user where deleted = 0";
	private static String GET_MAILING_USERS = "select * from cws_user where deleted = 0 and mailing = 1";

	public List<UserDto> getAllUsers(Transaction transaction) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();
			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_ALL_USERS);
			rs = ps.executeQuery();
			List<UserDto> users = new ArrayList<UserDto>();
			while (rs.next()) {
				users.add(makeUserDto(transaction, rs));
			}

			return users;

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

		return new ArrayList<UserDto>();

	}

	public List<UserDto> getMailingUsers(Transaction transaction) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();
			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_MAILING_USERS);
			rs = ps.executeQuery();
			List<UserDto> users = new ArrayList<UserDto>();
			while (rs.next()) {
				users.add(makeUserDto(transaction, rs));
			}

			return users;

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

		return new ArrayList<UserDto>();

	}

	private UserDto makeUserDto(Transaction transaction, ResultSet rs) throws SQLException {
		int user_id = rs.getInt(1);
		String user_name = rs.getString(2);
		String email = rs.getString(3);
		int team_id = rs.getInt(4);
		int deleted = rs.getInt(5);
		int mailing = rs.getInt(6);
		String access_token = rs.getString(7);
		return new UserDto(user_id, team_id, user_name, email, deleted, mailing, access_token);
	}

	private static final String GET_RANK = "select cu.* from cws_user as cu, cws_solution as cs where cu.user_id = cs.user_id and cs.problem_id = ? and cs.correct = 1 order by cs.post_time";

	public List<UserDto> getRank(Transaction transaction, int problem_id) throws SQLException {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			transaction.begin();
			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(GET_RANK);
			ps.setInt(1, problem_id);
			rs = ps.executeQuery();
			List<UserDto> users = new ArrayList<UserDto>();
			while (rs.next()) {
				users.add(makeUserDto(transaction, rs));
			}

			return users;

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			transaction.preDestroy();
		}

		return new ArrayList<UserDto>();
	}

	private static final String INSERT_USER = "insert into cws_user(user_name, email, team_id, deleted, mailing, access_token) values(?,?,99,0,0,?)";

	public void insertUser(Transaction transaction, String e) {
		PreparedStatement ps = null;
		try {
			transaction.begin();
			Connection conn = transaction.getResource(Connection.class);
			ps = conn.prepareStatement(INSERT_USER);
			ps.setString(1, e.substring(0, e.indexOf('@')));
			ps.setString(2, e);
			ps.setString(3, "");

			ps.executeUpdate();

		} catch (SQLException se) {

			se.printStackTrace();

		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException exception) {
					exception.printStackTrace();
				}
			}
			transaction.preDestroy();
		}
	}
}
